/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.tspp.dao;

import com.tspp.common.ResourceManager;
import com.tspp.dao.dto.Groupa;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


/**
 * Concrete realization of groupaDao interface. Retrieve the connection from pool in every method
 * @author Maks
 */
public class GroupaDaoImpl extends AbstractDao<Groupa> implements GroupaDao {
    /**
     * All finder methods in this class use this SELECT constant to build their queries
     */
    private static final String SQL_SELECT = "SELECT ID_GROUP, ID_SPECIALITY, MAX_STUDENTS_NUMBER, CODE FROM " + getTableName() + "";
    /**
     * SQL INSERT statement for this table
     */
    private static final String SQL_INSERT = "INSERT INTO " + getTableName() + " ( ID_SPECIALITY, MAX_STUDENTS_NUMBER, CODE ) VALUES ( ?, ?, ? )";
    /**
     * SQL UPDATE statement for this table
     */
    private static final String SQL_UPDATE = "UPDATE " + getTableName() + " SET ID_SPECIALITY = ?, MAX_STUDENTS_NUMBER = ?, CODE = ? WHERE ID_GROUP = ?";
    /**
     * SQL DELETE statement for this table
     */
    private static final String SQL_DELETE = "DELETE FROM " + getTableName() + " WHERE ID_GROUP = ?";
    /**
     * Index of column ID_GROUP
     */
    private static final int COLUMN_ID_GROUP = 1;
    /**
     * Index of column ID_SPECIALITY
     */
    private static final int COLUMN_ID_SPECIALITY = 2;
    /**
     * Index of column MAX_STUDENTS_NUMBER
     */
    private static final int COLUMN_MAX_STUDENTS_NUMBER = 3;
    /**
     * Index of column CODE
     */
    private static final int COLUMN_CODE = 4;

    /**
     * Inserts a new row in the GROUPA table.
     */
    public void insert(Groupa dto) throws DaoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ResourceManager.getConnection();
            stmt = conn.prepareStatement(SQL_INSERT);
            int index = 1;
            if (dto.getIdSpeciality() == -1) {
                stmt.setNull(index++, java.sql.Types.INTEGER);
            } else {
                stmt.setInt(index++, dto.getIdSpeciality());
            }
            stmt.setInt(index++, dto.getMaxStudentsNumber());
            stmt.setString(index++, dto.getCode());
            System.out.println("Executing " + SQL_INSERT + " with DTO: " + dto);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }

    }

    /**
     * Updates a single row in the GROUPA table.
     */
    public void update(int group, Groupa dto) throws DaoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {

            conn = ResourceManager.getConnection();
            //System.out.println("Executing " + SQL_UPDATE + " with DTO: " + dto);
            //stmt = conn.prepareStatement(SQL_UPDATE);
            int index = 1;
            if (dto.getIdSpeciality() == -1) {
                stmt.setNull(index++, java.sql.Types.INTEGER);
            } else {
                stmt.setInt(index++, dto.getIdSpeciality());
            }
            stmt.setInt(index++, dto.getMaxStudentsNumber());
            stmt.setString(index++, dto.getCode());
            stmt.setInt(index++, group);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }
    }

    /**
     * Deletes a single row in the GROUPA table.
     */
    public void delete(int group) throws DaoException {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = ResourceManager.getConnection();
            //System.out.println("Executing " + SQL_DELETE + " with PK: " + pk);
            stmt = conn.prepareStatement(SQL_DELETE);
            stmt.setInt(1, group);
            stmt.executeUpdate();
        } catch (Exception _e) {
            throw new DaoException("Exception: " + _e.getMessage(), _e);
        } finally {
            ResourceManager.close(stmt);
            ResourceManager.close(conn);
        }
    }

    /**
     * Returns all rows from the GROUPA table that match the criteria 'ID_GROUP = :idGroup'.
     */
    public Groupa findByPrimaryKey(int idGroup) throws DaoException {
        List<Groupa> ret = findByDynamicSelect(SQL_SELECT + " WHERE ID_GROUP = ?", new Object[]{new Integer(idGroup)});
        return ret.isEmpty() ? null : ret.get(0);
    }

    /**
     * Returns all rows from the GROUPA table that match the criteria ''.
     */
    public List<Groupa> findAll() throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " ORDER BY ID_GROUP", null);
    }

    /**
     * Returns all rows from the GROUPA table that match the criteria 'ID_SPECIALITY = :idSpeciality'.
     */
    public List<Groupa> findBySpeciality(int idSpeciality) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE ID_SPECIALITY = ?", new Object[]{new Integer(idSpeciality)});
    }

    /**
     * Returns all rows from the GROUPA table that match the criteria 'ID_GROUP = :idGroup'.
     */
    public List<Groupa> findWhereIdGroupEquals(int idGroup) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE ID_GROUP = ? ORDER BY ID_GROUP", new Object[]{new Integer(idGroup)});
    }

    /**
     * Returns all rows from the GROUPA table that match the criteria 'MAX_STUDENTS_NUMBER = :maxStudentsNumber'.
     */
    public List<Groupa> findWhereMaxStudentsNumberEquals(int maxStudentsNumber) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE MAX_STUDENTS_NUMBER = ? ORDER BY MAX_STUDENTS_NUMBER", new Object[]{new Integer(maxStudentsNumber)});
    }

    /**
     * Returns all rows from the GROUPA table that match the criteria 'CODE = :code'.
     */
    public List<Groupa> findWhereCodeEquals(String code) throws DaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE CODE = ? ORDER BY CODE", new Object[]{code});
    }

    /**
     * Return table name this dao is operated with
     * @return String name of table
     */
    public static String getTableName() {
        return "GROUPA";
    }


    /**
     * Implements abstract getDtoFrom of AbstractDao
     * @see AbstractDAO
     * @param rs
     * @return newly created transfer object
     * @throws SQLException
     */
    @Override
    protected Groupa getDtoFrom(ResultSet rs) throws SQLException {
        Groupa dto = new Groupa();
        dto.setIdGroup(rs.getInt(COLUMN_ID_GROUP));
        dto.setIdSpeciality(rs.getInt(COLUMN_ID_SPECIALITY));
        if (rs.wasNull()) {
            dto.setIdSpeciality(-1);
        }
        dto.setMaxStudentsNumber(rs.getInt(COLUMN_MAX_STUDENTS_NUMBER));
        dto.setCode(rs.getString(COLUMN_CODE));
        return dto;
    }
}
